from openpyxl import load_workbook
import time
from openpyxl import Workbook
import xlrd


class generateRoadList():
    def __init__(self,filepath,savepath,tested_all):
        self.excel_road_list = []
        self.filepath = filepath
        self.savepath = savepath
        self.tested_all = tested_all
        self.acquire_sheet()
        self.tested_road_list = self.create_tested_road_list()

        self.wb = Workbook()
        self.ws = self.wb.active

        self.workbook_xlrd = xlrd.open_workbook(r'{}'.format(filepath))
        self.sheet_xlrd = self.workbook_xlrd.sheet_by_name('Sheet1')

    def acquire_sheet(self):
        self.workbook = load_workbook(self.filepath)
        self.sheet = self.workbook["Sheet1"]  # 根据表名获取要操作的sheet页

    # 生成excel中的行号和路口值[{2:596}{}{}]
    def create_excel_road_item_list(self):
        cells = self.sheet["A"]
        row_num = 0
        for i in cells:
            row_num += 1
            if i.value == 'crossId':
                continue
            self.excel_road_list.append({row_num: i.value})
        return self.excel_road_list

    # 获取测试过的路口
    def create_tested_road_list(self):
        tested_road = self.tested_all.split(' ')
        return tested_road

    # 保存excel
    def savePath(self):
        self.workbook.save(savepath)

    # 删除excel一行，更新行号，循环执行
    def loop_update_excel(self):
        excel_road_item_list = self.create_excel_road_item_list()
        for item in excel_road_item_list:
            # print(item)
            current_excel_index = list(item.keys())[0]
            current_excel_vlaue = list(item.values())[0]
            # print(current_excel_index, current_excel_vlaue)
            if str(current_excel_vlaue) in self.tested_road_list:
                self.ws.append(self.sheet_xlrd.row_values(current_excel_index-1))
                # self.sheet.delete_rows(current_excel_index)  # 删除当前行
                # 删掉一行后，重新组装excel的路口号和索引
                if (current_excel_index-1)==len(excel_road_item_list):
                    # 如果是excel最后一个值是测试过的路口，直接返回
                    return
                # self.excel_road_list.clear()
                # self.loop_update_excel()
        # print(excel_road_item_list)

    def main(self):
        self.loop_update_excel()
        # self.savePath()


if __name__ == '__main__':
    filepath = r"/Users/v_baiguanghui/Desktop/苏州出差/稳定性excel/356全量路口.xlsx"
    savepath = r'/Users/v_baiguanghui/Desktop/苏州出差/result/result_'+str(time.strftime("%Y-%m-%d_%H_%M_%S",time.localtime(time.time())))+'.xlsx'
    # 要测试的路口列表
    # tested_road_19 = '299 144 325 241 551 296 308 457 439 242 402 550 362 1 370 244 553 475 83 114 495 127 283 516 408 260 210 181' \
    #                  '130 78 229 401 495 165 426'

    tested_road_20 = '476 226 272 247 502 316 399 86 497 338 286 51 269 213 255 329 566 171 50 392 192 572 120 112 231 80 463 367 519 133 484 422 279 508 491 196 19 240 423 107 470 277 301 46 199'
    tested_road_21 = '549 457  547 439 558 242 258 383 362 504 400 24 333 310 350 36 202 83 544 500 109 506 114 405 58 357 30 408 302 461 159 346 560 414 260 181 130 229 182 507 122 164 153'
    tested_road_22 = '334 343 573 248 492 147 379 542 41 158 305 91 257 441 363 554 419 285 474 446 321 526 409 394 137 485 525 256 186 211 176 111 375 488 65 377 218 237 427 382 395 578 276 223 380 207 223 445 267 190 531 468 149 320 154 85 139 45 59 569 168 418 561 415 77 103 117'
    tested_road_23 = '413 517 486 66 493 151 55 412 514 101 201 331 102 37 98 520 431 450 464 570 386 446 518 516 116 166 96 255 313 136 530 361 20'
    tested_road_RD = '5 17 21 22 25 29 45 47 56 59 65 77 82 103 105 126 138 154 185 186 189 190 203 207 215 216 218 256 261 263 267 287 337 347 358 372 373 376 387 390 394 410 424 438 445 448 473 479 490 494 496 498 522 531 532 535 546 567'
    tested_road_tuijian = '494 47 490 522 5 424 263 21 387 448 479 535 496 126 22 347 216 546 390 215 567 56 189 473 372 438'
    tested_all = tested_road_tuijian

    obj = generateRoadList(filepath,savepath,tested_all)
    obj.main()


    obj.wb.save(savepath)